package com.safety51.bi.service;

import com.safety51.bi.entity.ChartConfig;
import com.safety51.bi.entity.DimensionConfig;
import com.safety51.bi.entity.MetricConfig;
import com.safety51.bi.entity.FilterConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Service
public class DataQueryService {
    
    private static final Logger log = LoggerFactory.getLogger(DataQueryService.class);
    
    private final JdbcTemplate jdbcTemplate;
    
    public DataQueryService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    public List<Map<String, Object>> executeQuery(ChartConfig config, Map<String, Object> frontendFilters, Integer page, Integer pageSize) {
        QueryBuilder queryBuilder = new QueryBuilder(config, frontendFilters);
        String sql = queryBuilder.buildSql();
        List<Object> params = queryBuilder.getParams();
        // 添加分页
        if (page != null && pageSize != null && page > 0 && pageSize > 0) {
            int offset = (page - 1) * pageSize;
            sql += " LIMIT ? OFFSET ?";
            params.add(pageSize);
            params.add(offset);
        }
        log.info("执行SQL: {}", sql);
        log.info("参数: {}", params);
        
        if (params.isEmpty()) {
            return jdbcTemplate.query(sql, new MapRowMapper());
        } else {
            return jdbcTemplate.query(sql, params.toArray(), new MapRowMapper());
        }
    }

    // 添加获取总数的方法
    public Long getTotalCount(ChartConfig config, Map<String, Object> frontendFilters) {
        QueryBuilder queryBuilder = new QueryBuilder(config, frontendFilters);
        String countSql = "SELECT COUNT(*) as total FROM (" + queryBuilder.buildSql() + ") as count_table";
        List<Object> params = queryBuilder.getParams();

        try {
            if (params.isEmpty()) {
                return jdbcTemplate.queryForObject(countSql, Long.class);
            } else {
                return jdbcTemplate.queryForObject(countSql, params.toArray(), Long.class);
            }
        } catch (Exception e) {
            log.error("获取总数失败: {}", countSql, e);
            return 0L;
        }
    }

    /**
     * SQL查询构建器 - 修复版
     */
    private static class QueryBuilder {
        private final ChartConfig config;
        private final Map<String, Object> frontendFilters;
        private final List<Object> params = new ArrayList<>();
        private final List<String> whereConditions = new ArrayList<>();
        
        public QueryBuilder(ChartConfig config, Map<String, Object> frontendFilters) {
            this.config = config;
            this.frontendFilters = frontendFilters != null ? frontendFilters : new HashMap<>();
        }
        
        public String buildSql() {
            StringBuilder sql = new StringBuilder("SELECT ");
            
            // 构建SELECT部分
            buildSelectClause(sql);
            
            // FROM部分
            sql.append(" FROM ").append(config.getDataSource());
            
            // WHERE部分
            buildWhereClause();
            if (!whereConditions.isEmpty()) {
                sql.append(" WHERE ").append(String.join(" AND ", whereConditions));
            }
            
            // GROUP BY部分
            buildGroupByClause(sql);
            
            // ORDER BY部分
            buildOrderByClause(sql);
            
            return sql.toString();
        }

        private void buildSelectClause(StringBuilder sql) {
            List<String> selectItems = new ArrayList<>();

            // 如果是表格显示类型且没有聚合指标，则查询明细数据
            boolean isDetailQuery = "table".equals(config.getDisplayType()) &&
                    (config.getMetrics() == null ||
                            config.getMetrics().isEmpty() ||
                            isDetailMetrics(config.getMetrics()));

            if (isDetailQuery) {
                // 明细查询：直接选择字段，不进行聚合
                if (config.getDimensions() != null) {
                    for (DimensionConfig dimension : config.getDimensions()) {
                        selectItems.add(dimension.getField() + " AS `" + dimension.getAlias() + "`");
                    }
                }
                if (config.getMetrics() != null) {
                    for (MetricConfig metric : config.getMetrics()) {
                        // 对于明细查询，如果表达式是聚合函数，需要去掉聚合
                        String expression = metric.getExpression();
                        if (isAggregateFunction(expression)) {
                            // 提取字段名，去掉聚合函数
                            expression = extractFieldFromAggregate(expression);
                        }
                        selectItems.add(expression + " AS `" + metric.getAlias() + "`");
                    }
                }

                // 如果没有指定任何字段，选择所有字段
                if (selectItems.isEmpty()) {
                    selectItems.add("*");
                }
            } else {
                // 聚合查询：原有逻辑
                if (config.getMetrics() != null && !config.getMetrics().isEmpty()) {
                    for (MetricConfig metric : config.getMetrics()) {
                        String expression = metric.getExpression();
                        String alias = metric.getAlias();
                        selectItems.add(expression + " AS `" + alias + "`");
                    }
                } else {
                    selectItems.add("COUNT(*) AS count");
                }

                if (config.getDimensions() != null && !config.getDimensions().isEmpty()) {
                    for (DimensionConfig dimension : config.getDimensions()) {
                        selectItems.add(dimension.getField() + " AS `" + dimension.getAlias() + "`");
                    }
                }
            }

            sql.append(String.join(", ", selectItems));
        }

        // 判断是否为明细指标（没有聚合函数）
        private boolean isDetailMetrics(List<MetricConfig> metrics) {
            for (MetricConfig metric : metrics) {
                if (isAggregateFunction(metric.getExpression())) {
                    return false;
                }
            }
            return true;
        }

        // 判断是否为聚合函数
        private boolean isAggregateFunction(String expression) {
            if (expression == null) return false;
            String upperExpr = expression.toUpperCase();
            return upperExpr.contains("SUM(") ||
                    upperExpr.contains("COUNT(") ||
                    upperExpr.contains("AVG(") ||
                    upperExpr.contains("MAX(") ||
                    upperExpr.contains("MIN(");
        }

        // 从聚合函数中提取字段名
        private String extractFieldFromAggregate(String expression) {
            // 简单实现：提取括号内的内容
            int start = expression.indexOf('(');
            int end = expression.lastIndexOf(')');
            if (start != -1 && end != -1 && end > start) {
                return expression.substring(start + 1, end).trim();
            }
            return expression;
        }


        
        private void buildWhereClause() {
            // 处理配置中的固定过滤条件
            if (config.getFilters() != null) {
                for (FilterConfig filter : config.getFilters()) {
                    addFilterCondition(filter);
                }
            }
            
            // 处理前端传递的动态过滤条件
            processFrontendFilters();
        }
        
        private void addFilterCondition(FilterConfig filter) {
            if (filter == null || filter.getField() == null) {
                return;
            }
            
            String field = filter.getField();
            String operator = filter.getOperator() != null ? filter.getOperator() : "eq";
            Object value = filter.getValue();
            
            switch (operator.toLowerCase()) {
                case "eq":
                    whereConditions.add(field + " = ?");
                    params.add(value);
                    break;
                case "gt":
                    whereConditions.add(field + " > ?");
                    params.add(value);
                    break;
                case "lt":
                    whereConditions.add(field + " < ?");
                    params.add(value);
                    break;
                case "like":
                    whereConditions.add(field + " LIKE ?");
                    params.add("%" + value + "%");
                    break;
                case "between":
                    if (value instanceof List && ((List<?>) value).size() == 2) {
                        List<?> range = (List<?>) value;
                        whereConditions.add(field + " BETWEEN ? AND ?");
                        params.add(range.get(0));
                        params.add(range.get(1));
                    }
                    break;
                default:
                    // 默认使用等于
                    whereConditions.add(field + " = ?");
                    params.add(value);
            }
        }
        
        private void processFrontendFilters() {
            if (frontendFilters == null || frontendFilters.isEmpty()) {
                return;
            }
            
            for (Map.Entry<String, Object> entry : frontendFilters.entrySet()) {
                String key = entry.getKey();
                Object value = entry.getValue();
                
                // 特殊处理 dateRange 参数
                if ("dateRange".equals(key)) {
                    if (value instanceof List && ((List<?>) value).size() == 2) {
                        List<?> dateRange = (List<?>) value;
                        whereConditions.add("date BETWEEN ? AND ?");
                        params.add(dateRange.get(0));
                        params.add(dateRange.get(1));
                    }
                } else {
                    // 其他参数作为普通等于条件处理
                    whereConditions.add(key + " = ?");
                    params.add(value);
                }
            }
        }

        // 在buildGroupByClause方法中添加条件
        private void buildGroupByClause(StringBuilder sql) {
            boolean isDetailQuery = "table".equals(config.getDisplayType()) &&
                    (config.getMetrics() == null ||
                            config.getMetrics().isEmpty() ||
                            isDetailMetrics(config.getMetrics()));
            // 明细查询不需要GROUP BY
            if (isDetailQuery) {
                return;
            }

            if (config.getDimensions() != null && !config.getDimensions().isEmpty()) {
                sql.append(" GROUP BY ");
                List<String> groupByFields = new ArrayList<>();
                for (DimensionConfig dimension : config.getDimensions()) {
                    groupByFields.add(dimension.getField());
                }
                sql.append(String.join(", ", groupByFields));
            }
        }
        
        private void buildOrderByClause(StringBuilder sql) {
            if (config.getMetrics() != null && !config.getMetrics().isEmpty()) {
                sql.append(" ORDER BY `");
                sql.append(config.getMetrics().get(0).getAlias());
                sql.append("` DESC");
            }
        }
        
        public List<Object> getParams() {
            return params;
        }
    }
    
    private static class MapRowMapper implements RowMapper<Map<String, Object>> {
        @Override
        public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
            Map<String, Object> map = new LinkedHashMap<>(); // 保持字段顺序
            int columnCount = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rs.getMetaData().getColumnLabel(i);
                Object value = rs.getObject(i);
                map.put(columnName, value);
            }
            return map;
        }
    }
}